from odoo import fields, models, api


class SalePlanYieldRate(models.Model):
    _name = 's.sale.plan.yield.rate'
    _description = 'Sale Plan Yield Rate'
    _auto = False

    sale_id = fields.Many2one('sale.order', 'sale')
    sale_line_id = fields.Many2one('sale.order.line', 'sale line')
    product_id = fields.Many2one('product.product', 'Product')
    order_qty = fields.Integer('order qty')
    plan_qty = fields.Integer('plan qty')
    plan_yield_rate = fields.Float('plan yield rate')
    product_type = fields.Selection([
        ('N', 'batch'),
        ('S', 'sample'),
    ], string='Product Type')
    feeding_date = fields.Datetime('Material Staging Date', compute='_compute_feeding_date')

    @api.depends('product_id')
    def _compute_feeding_date(self):
        for rec in self:
            production_ids = rec.sale_line_id.s_mrp_production_ids
            if production_ids:
                rec.feeding_date = production_ids[0].create_date
            else:
                rec.feeding_date = None

    def _compute_yield_rate(self):
        for rec in self:
            rec.yield_rate = 0

    @property
    def _table_query(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        product_id = self._context.get('product_id')
        product_type = self._context.get('product_type', '')
        where = ''
        where_list = []
        if start_date or end_date:
            domain = [('mo_type', '=', 'N')]
            if start_date:
                domain.append(('create_date', '>=', start_date))
            if end_date:
                domain.append(('create_date', '<', end_date))
            production_ids = self.env['mrp.production'].search(domain)
            if production_ids:
                sale_line_ids = production_ids.mapped('s_sale_order_line_ids')
                sol_ids_str = ','.join(str(i) for i in sale_line_ids.ids)
                where_list.append(f" sol.id in ({sol_ids_str})")
        if product_id:
            where_list.append(f' pp.id = {product_id} ')
        if product_type and product_type != 'all':
            if product_type == 'batch':
                where_list.append(" pt.materiel_status = 'P' ")
            elif product_type == 'sample':
                where_list.append(" pt.materiel_status != 'P'  ")

        if where_list:
            where = 'and ' + ' and '.join(where_list)
        select_sql = f"""
            select sol.id                                                   id,
                   so.id                                                    sale_id,
                   sol.product_id                                           product_id,
                   sol.product_uom_qty                                      order_qty,
                   sol.id                                                   sale_line_id,
                   sol.s_normal_planned_qty                                 plan_qty,
                   case
                       when sol.s_normal_planned_qty > 0 then sol.product_uom_qty / sol.s_normal_planned_qty
                       else 0 end                                           plan_yield_rate,
                   case when pt.materiel_status = 'P' then 'N' else 'S' end product_type
            from sale_order_line sol
                     left join sale_order so on sol.order_id = so.id
                     left join product_product pp on sol.product_id = pp.id
                     left join product_template pt on pp.product_tmpl_id = pt.id
            where sol.s_planned_qty > 0
              and sol.s_normal_planned_qty > 0
              {where}
            order by so.id
            """
        return select_sql
